﻿using Goooodbye.Core.Entities;
using Goooodbye.Core.Models;
using Goooodbye.Core.Models.Query;
using Goooodbye.Core.Repositories;
using Dapper;
using Goooodbye.Infrastructure.Repositories.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Goooodbye.Infrastructure.Repositories
{
    public class DictRepository : Repository, IDictRepository
    {
        public DictRepository(IDbConnection conn) : base(conn) { }

        public int Count(DictQuery query)
        {
            var sql = $"SELECT COUNT(*) FROM dict";
            if (query != null)
            {
                sql += BuildWhereClause(query);
            }
            return conn.ExecuteScalar<int>(sql, query);
        }

        public int Delete(string id)
        {
            return conn.Execute($"DELETE FROM dict WHERE id = @Id", new { Id = id });
        }

        public int DeleteAll()
        {
            return conn.Execute("DELETE FROM dict");
        }

        public DictModel Select(string id)
        {
            return conn.QuerySingleOrDefault<DictModel>($"SELECT id, term, mapping, note FROM dict WHERE id = @Id", new { Id = id });
        }

        public int Insert(Dict entity)
        {
            return conn.Execute("INSERT INTO dict(id, term, mapping, note) VALUES(@Id, @Term, @Mapping, @Note)", entity);
        }

        public IList<DictModel> Select(DictQuery query)
        {
            var sql = $"SELECT id, term, mapping, note FROM dict";
            if (query != null)
            {
                sql += BuildWhereClause(query)
                    + $" ORDER BY {(String.IsNullOrEmpty(query.SortOrder) ? "id" : query.SortOrder)}"
                    + (query.Pageable ? $" LIMIT {(query.PageNum - 1) * query.PageSize}, {query.PageSize}" : String.Empty);
            }
            return conn.Query<DictModel>(sql, query).ToList();
        }

        public int Update(Dict entity)
        {
            return conn.Execute("UPDATE dict SET term = @Term, mapping = @Mapping, note = @Note WHERE id = @Id", entity);
        }

        private string BuildWhereClause(DictQuery query)
        {
            var ret = " WHERE 1=1";
            if (!String.IsNullOrEmpty(query.Keyword))
            {
                ret += $" AND (term like '%'||@Keyword||'%' OR mapping like '%'||@Keyword||'%' OR note like '%'||@Keyword||'%')";
            }
            return ret;
        }
    }
}
